package DB_Layer;
import Model_Layer.*;

import java.sql.*;
import java.util.ArrayList;

public class DB_Zipcode_City implements IFDB_Zipcode_City {
	private Connection con;
	
	public DB_Zipcode_City() {
		con = DbConnection.getInstance().getDBcon();
	}

	public ArrayList<Zipcode_City> getAllZipCode_City(boolean retriveAssociation) {
		return miscWhere("", retriveAssociation);
		
	}
	//Find zipcode having the zipcode
	public Zipcode_City findZipcode(int zipcode, boolean retriveAssociation) {
		String wClause = " ZipCode = '" + zipcode + "'";
		return singleWhere(wClause, retriveAssociation);
	}
	//Find city
	public Zipcode_City findCity(String city, boolean retriveAssociation) {
		String wClause = "City like '%" + city + "%'";
		System.out.println("SearchZipCode " + wClause);
		return singleWhere(wClause, retriveAssociation);
	}
	//Insert a new Zipcode_City
	public int insertZipcode_City(Zipcode_City zip) throws Exception {
		int rc = -1;
		String query = "Insert INTO ZipCode_City VALUES('"+
				zip.getZipcode() + "','" +
				zip.getCity() + "','";
		
		System.out.println("insert : " + query);
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(SQLException ex)
		{
			System.out.println("Error in inserting new ZipCode_City");
			throw new Exception ("ZipCode is not inserted correct");
		}
		return (rc);
	}

	public int updateZipcode_City(Zipcode_City zip) {
		Zipcode_City zipObj = zip;
		int rc = -1;
		
		String query = "UPDATE ZipCode_City SET "+
				"ZipCode ='"+ zipObj.getZipcode()+"',"+
				"City = "+ zipObj.getCity()+"',"+
			" WHERE ZipCode = '"+ zipObj.getZipcode() +"'";
		System.out.println("Update query:" + query);
		
		try{//Update ZipCode
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			
			stmt.close();
		}//end try
		catch (Exception ex){
			System.out.println("Update exception in ZipCode_City db: "+ex);
		}
		return (rc);
	}
	
	public int delete(String zipcode)
	{
		int rc=-1;
		
		String query = "DELETE FROM ZipCode_City WHERE ZipCode = '" + zipcode + "'";
			System.out.println(query);
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch (Exception ex)
		{
			System.out.println("Delete exception in Zipcode_City db: " + ex);
		}
		return(rc);
	}
	
	private ArrayList<Zipcode_City> miscWhere(String wClause, boolean retriveAssociation)
	{
		ResultSet results;
		ArrayList<Zipcode_City> list = new ArrayList<Zipcode_City>();
		
		String query = buildQuery(wClause);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(2);
			results = stmt.executeQuery(query);
			
			while( results.next() )
			{
					Zipcode_City zipObj = new Zipcode_City();
				zipObj = buildZipcode_code(results);
					list.add(zipObj);
			}
				stmt.close();
				
		}
		catch(Exception e)
		{
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}
	
	
	//Singelwhere is used when we only select one
	private Zipcode_City singleWhere(String wClause, boolean retriveAssociation)
	{
		ResultSet results;
		Zipcode_City zipObj = new Zipcode_City();
		
			String query = buildQuery(wClause);
				System.out.println(query);
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if(results.next())
			{
				zipObj = buildZipcode_code(results);
				stmt.close();
			}
			else
			{
				zipObj = null;
			}
		}
		catch(Exception e)
		{
			System.out.println("Query exception: "+e);
		}
		return zipObj;
	}
	
	//method to build the query
	private String buildQuery(String wClause)
	{
		String  query = "SELECT ZipCode,City FROM ZipCode_City";
		
		if(wClause.length()>0)
			query = query + " WHERE " + wClause;
		
		return query;
	}
	
	//method to build the Zipcode_city object
	private Zipcode_City buildZipcode_code(ResultSet results)
	{
		Zipcode_City zipObj = new Zipcode_City();
		try{
			zipObj.setZipcode(results.getInt("ZipCode"));
			zipObj.setCity(results.getString("City"));
		}
		catch(Exception e)
		{
			System.out.println("Error in building the Zipcode object");
		}
		return zipObj;
	}
	
	

}
